
<!DOCTYPE html>

<html lang="en">
  <head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>第四章 Excel函数-逻辑判断 &#8212; free-excel</title>
<script>
  document.documentElement.dataset.mode = localStorage.getItem("mode") || "";
  document.documentElement.dataset.theme = localStorage.getItem("theme") || "light"
</script>

  <!-- Loaded before other Sphinx assets -->
  <link href="../_static/styles/theme.css?digest=92025949c220c2e29695" rel="stylesheet">
<link href="../_static/styles/pydata-sphinx-theme.css?digest=92025949c220c2e29695" rel="stylesheet">


  <link rel="stylesheet"
    href="../_static/vendor/fontawesome/5.13.0/css/all.min.css">
  <link rel="preload" as="font" type="font/woff2" crossorigin
    href="../_static/vendor/fontawesome/5.13.0/webfonts/fa-solid-900.woff2">
  <link rel="preload" as="font" type="font/woff2" crossorigin
    href="../_static/vendor/fontawesome/5.13.0/webfonts/fa-brands-400.woff2">

    <link rel="stylesheet" type="text/css" href="../_static/pygments.css" />
    <link rel="stylesheet" type="text/css" href="../_static/css/s4defs-roles.css" />

  <!-- Pre-loaded scripts that we'll load fully later -->
  <link rel="preload" as="script" href="../_static/scripts/pydata-sphinx-theme.js?digest=92025949c220c2e29695">

    <script data-url_root="../" id="documentation_options" src="../_static/documentation_options.js"></script>
    <script src="../_static/jquery.js"></script>
    <script src="../_static/underscore.js"></script>
    <script src="../_static/_sphinx_javascript_frameworks_compat.js"></script>
    <script src="../_static/doctools.js"></script>
    <script src="../_static/sphinx_highlight.js"></script>
    <link rel="shortcut icon" href="../_static/logo.ico"/>
    <link rel="index" title="Index" href="../genindex.html" />
    <link rel="search" title="Search" href="../search.html" />
    <link rel="next" title="第五章 Excel函数-文本函数" href="chap5.html" />
    <link rel="prev" title="第三章 Excel的表合并" href="chap3.html" />
<meta name="viewport" content="width=device-width, initial-scale=1" />
<meta name="docsearch:language" content="en">
  </head>
  
  
  <body data-spy="scroll" data-target="#bd-toc-nav" data-offset="180" data-default-mode="">
    <div class="bd-header-announcement container-fluid" id="banner">
      

    </div>

    
    <nav class="bd-header navbar navbar-light navbar-expand-lg bg-light fixed-top bd-navbar" id="navbar-main"><div class="bd-header__inner container-xl">

  <div id="navbar-start">
    
    
  


<a class="navbar-brand logo" href="../index.html">
  
  
  
  
    <img src="../_static/logo.svg" class="logo__image only-light" alt="Logo image">
    <img src="../_static/logo.svg" class="logo__image only-dark" alt="Logo image">
  
  
</a>
    
  </div>

  <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbar-collapsible" aria-controls="navbar-collapsible" aria-expanded="false" aria-label="Toggle navigation">
    <span class="fas fa-bars"></span>
  </button>

  
  <div id="navbar-collapsible" class="col-lg-9 collapse navbar-collapse">
    <div id="navbar-center" class="mr-auto">
      
      <div class="navbar-center-item">
        <ul id="navbar-main-elements" class="navbar-nav">
    <li class="toctree-l1 nav-item">
 <a class="reference internal nav-link" href="../%E4%B8%BB%E9%A1%B5.html">
  主页
 </a>
</li>

<li class="toctree-l1 current active nav-item">
 <a class="reference internal nav-link" href="index.html">
  教程
 </a>
</li>

<li class="toctree-l1 nav-item">
 <a class="reference internal nav-link" href="../%E4%BD%9C%E8%80%85.html">
  作者
 </a>
</li>

<li class="toctree-l1 nav-item">
 <a class="reference internal nav-link" href="../Datawhale.html">
  Datawhale
 </a>
</li>

<li class="toctree-l1 nav-item">
 <a class="reference internal nav-link" href="../%E7%BB%83%E4%B9%A0%E9%A2%98%E5%8F%82%E8%80%83%E6%93%8D%E4%BD%9C.html">
  练习题参考操作
 </a>
</li>

    
</ul>
      </div>
      
    </div>

    <div id="navbar-end">
      
      <div class="navbar-end-item">
        <span id="theme-switch" class="btn btn-sm btn-outline-primary navbar-btn rounded-circle">
    <a class="theme-switch" data-mode="light"><i class="fas fa-sun"></i></a>
    <a class="theme-switch" data-mode="dark"><i class="far fa-moon"></i></a>
    <a class="theme-switch" data-mode="auto"><i class="fas fa-adjust"></i></a>
</span>
      </div>
      
      <div class="navbar-end-item">
        <ul id="navbar-icon-links" class="navbar-nav" aria-label="Icon Links">
        <li class="nav-item">
          <a class="nav-link" href="https://github.com/datawhalechina/free-excel" rel="noopener" target="_blank" title="GitHub"><span><i class="fab fa-github-square"></i></span>
            <label class="sr-only">GitHub</label></a>
        </li>
      </ul>
      </div>
      
    </div>
  </div>
</div>
    </nav>
    

    <div class="bd-container container-xl">
      <div class="bd-container__inner row">
          

<!-- Only show if we have sidebars configured, else just a small margin  -->
<div class="bd-sidebar-primary col-12 col-md-3 bd-sidebar">
  <div class="sidebar-start-items"><form class="bd-search d-flex align-items-center" action="../search.html" method="get">
  <i class="icon fas fa-search"></i>
  <input type="search" class="form-control" name="q" id="search-input" placeholder="在这里搜索" aria-label="在这里搜索" autocomplete="off" >
</form><nav class="bd-links" id="bd-docs-nav" aria-label="Main navigation">
  <div class="bd-toc-item active">
    <ul class="current nav bd-sidenav">
 <li class="toctree-l1">
  <a class="reference internal" href="chap1.html">
   第一章 Excel与数据格式
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap2.html">
   第二章 Excel的快捷操作
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap3.html">
   第三章 Excel的表合并
  </a>
 </li>
 <li class="toctree-l1 current active">
  <a class="current reference internal" href="#">
   第四章 Excel函数-逻辑判断
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap5.html">
   第五章 Excel函数-文本函数
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap6.html">
   第六章 Excel函数-查找函数
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap7.html">
   第七章 Excel函数-动态函数
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap8.html">
   第八章Excel数据可视化
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap9.html">
   第九章Excel数据透视
  </a>
 </li>
 <li class="toctree-l1">
  <a class="reference internal" href="chap10.html">
   第十章Excel看板
  </a>
 </li>
</ul>

  </div>
</nav>
  </div>
  <div class="sidebar-end-items">
  </div>
</div>


          


<div class="bd-sidebar-secondary d-none d-xl-block col-xl-2 bd-toc">
  
    
    <div class="toc-item">
      
<div class="tocsection onthispage mt-5 pt-1 pb-3">
    <i class="fas fa-list"></i> On this page
</div>

<nav id="bd-toc-nav">
    <ul class="visible nav section-nav flex-column">
 <li class="toc-h2 nav-item toc-entry">
  <a class="reference internal nav-link" href="#if">
   1.if函数
  </a>
 </li>
 <li class="toc-h2 nav-item toc-entry">
  <a class="reference internal nav-link" href="#ifs">
   2.ifs函数
  </a>
 </li>
 <li class="toc-h2 nav-item toc-entry">
  <a class="reference internal nav-link" href="#sumif">
   3.sumif函数
  </a>
  <ul class="nav section-nav flex-column">
   <li class="toc-h3 nav-item toc-entry">
    <a class="reference internal nav-link" href="#header-n39">
     案例1
    </a>
   </li>
   <li class="toc-h3 nav-item toc-entry">
    <a class="reference internal nav-link" href="#header-n47">
     案例2
    </a>
   </li>
   <li class="toc-h3 nav-item toc-entry">
    <a class="reference internal nav-link" href="#header-n53">
     案例3
    </a>
   </li>
   <li class="toc-h3 nav-item toc-entry">
    <a class="reference internal nav-link" href="#header-n63">
     案例4
    </a>
   </li>
  </ul>
 </li>
 <li class="toc-h2 nav-item toc-entry">
  <a class="reference internal nav-link" href="#countifs">
   4.countifs函数
  </a>
 </li>
 <li class="toc-h2 nav-item toc-entry">
  <a class="reference internal nav-link" href="#header-n78">
   练习
  </a>
 </li>
</ul>

</nav>
    </div>
    
    <div class="toc-item">
      
    </div>
    
  
</div>


          
          
          <div class="bd-content col-12 col-md-9 col-xl-7">
              
              <article class="bd-article" role="main">
                
  <div class="section" id="excel">
<span id="header-n0"></span><h1>第四章 Excel函数-逻辑判断<a class="headerlink" href="#excel" title="Permalink to this heading">#</a></h1>
<blockquote>
<div><p>Excel中有着非常丰富的函数，这些函数能大幅度提升我们的工作效率，本节中会重点介绍一些常用的Excel函数。</p>
</div></blockquote>
<div class="section" id="if">
<span id="header-n4"></span><h2>1.if函数<a class="headerlink" href="#if" title="Permalink to this heading">#</a></h2>
<p>打开文件<code class="docutils literal notranslate"><span class="pre">data/chap4/4.1.xlsx</span></code>，点击【案例1】，现在需要对客户的原始得分进行校正，当客户的原始得分为负时，校正为0</p>
<p>针对上面遇到的问题，可以使用IF函数，Excel函数用法如下</p>
<p>【IF函数】<strong>=IF(判断条件，如果满足则，如果不满足则)</strong></p>
<p>针对遇到的问题，那么在C2单元格中输入</p>
<p><strong>=IF(B2&lt;0,0,B2)</strong> —-&gt; <strong>注意，这里的逗号要使用英文的逗号</strong></p>
<p>那么这个公式是什么意思呢？判断B2单元格是不是小于0，如果是，则C2单元格为0，如果不是则返回B2单元格的值，可以看到C2单元格实际返回值为0，这是因为B2的值为-1，满足判断条件，因此C2单元格返回为0</p>
<p>那么如何进行公式批量填充呢？</p>
<p>这里推荐一个比较优雅的方法</p>
<p>1.在C2单元格写Excel公式</p>
<p>2.选择需要填充公式的区域，包括C2单元格</p>
<p>3.点击C2单元格，进入公式编辑状态</p>
<p>4.使用快捷键<code class="docutils literal notranslate"><span class="pre">CTRL</span></code>+<code class="docutils literal notranslate"><span class="pre">Enter</span></code>进行批量填充</p>
<div class="figure align-default">
<img alt="" src="../_images/4.1.gif" />
</div>
<p>打开文件<code class="docutils literal notranslate"><span class="pre">data/chap4/4.1.xlsx</span></code>，点击【案例2】，现在有客户的1月、2月消费金额，如果客户2个月的消费金额都超过400，那么就认为这个客户为高消费客户。</p>
<p>这里与5.1中的案例不同，需要IF函数与逻辑函数进行配合</p>
<p>【AND函数】<strong>=AND(条件1，条件2，…)</strong></p>
<p>【OR函数】<strong>=OR(条件，条件2，…)</strong></p>
<p>AND函数，需要所有条件全部满足，才返还TRUE，OR函数，所有条件中只要有1个条件满足，就返还TRUE</p>
<p>针对遇到的问题，那么在D2单元格中输入</p>
<p><strong>=IF(AND(B2&gt;400,C2&gt;400),”高消费客户”,””)</strong> —–&gt; 这里””代表返回空值</p>
<p>随后对整列的函数进行填充，具体填充方法见5.1中详解</p>
<div class="figure align-default">
<img alt="" src="../_images/4.2.gif" />
</div>
</div>
<div class="section" id="ifs">
<span id="header-n27"></span><h2>2.ifs函数<a class="headerlink" href="#ifs" title="Permalink to this heading">#</a></h2>
<p>打开文件<code class="docutils literal notranslate"><span class="pre">data/chap4/4.1.xlsx</span></code>，点击【案例3】，有员工的相关考核分，考核得分在4.8分以上，优秀；考核得分在3.5-4.7分，良好；考核得分在0-3.4分，一般。</p>
<p>这里可以使用IFS函数，具体用法为</p>
<p>【IFS函数】=IFS(判断条件1,如果满足条件1则,判断条件2,如果满足条件2则)</p>
<p>针对遇到的问题，则可以在C2单元格输入</p>
<p><strong>=IFS(B3&lt;=3.4,”一般”,AND(B3&gt;=3.5,B3&lt;=4.7),”良好”,B3&gt;=4.8,”优秀”)</strong></p>
<p>对整列数据进行函数填充</p>
<div class="figure align-default">
<img alt="" src="../_images/4.3.gif" />
</div>
</div>
<div class="section" id="sumif">
<span id="header-n35"></span><h2>3.sumif函数<a class="headerlink" href="#sumif" title="Permalink to this heading">#</a></h2>
<blockquote>
<div><p>在Excel数据任务处理中，会遇到大量的求和的任务，SUMIF函数在求和任务中具有天然优势，因此在本节中将详细的讲解SUMIF函数的相关用法</p>
</div></blockquote>
<p>【SUMIF函数】=SUMIF(条件区域，求和条件，[实际求和区域])
—&gt;实际求和区域位选择条件</p>
<div class="section" id="header-n39">
<span id="id1"></span><h3>案例1<a class="headerlink" href="#header-n39" title="Permalink to this heading">#</a></h3>
<p>打开<code class="docutils literal notranslate"><span class="pre">data/chap4/4.2xlsx</span></code>中的【案例1】现在有几名同学的考试成绩，现在想统计数学成绩在90分以上的成绩之和</p>
<p>那么可以输入如下的函数公式</p>
<p><strong>=SUMIF(B:B,”&gt;=90”,B:B)</strong></p>
<p>第一个参数为条件区域，因为要判断数学成绩，所以这里选择B列，当然这里也可以修改为实际的区域B2:B12</p>
<p>第二个参数为判定条件，判定条件为大于等于90</p>
<p>第三个参数为实际求和区，这是选填参数</p>
<div class="figure align-default">
<img alt="" src="../_images/4.4.gif" />
</div>
</div>
<div class="section" id="header-n47">
<span id="id2"></span><h3>案例2<a class="headerlink" href="#header-n47" title="Permalink to this heading">#</a></h3>
<p>打开<code class="docutils literal notranslate"><span class="pre">data/chap4/4.2xlsx</span></code>中的【案例2】，现在想统计小米系列的产品销售额</p>
<p>SUMIF函数支持模糊匹配，因此我们输入如下公式</p>
<p><strong>=SUMIF(B:B,”小米*”,C:C)</strong></p>
<p>使用小米*匹配所有的小米系产品</p>
<div class="figure align-default">
<img alt="" src="../_images/4.5.gif" />
</div>
</div>
<div class="section" id="header-n53">
<span id="id3"></span><h3>案例3<a class="headerlink" href="#header-n53" title="Permalink to this heading">#</a></h3>
<p>打开<code class="docutils literal notranslate"><span class="pre">data/chap4/4.2xlsx</span></code>中的【案例3】，现在想统计1月-3月的预计销售额预计销售和实际销售额</p>
<p>因此可以巧用SUMIF函数，将条件区域进行横向选择，条件选择自己需要的字段，因此输入如下两个函数公式，并向下拖拽填充其他位置</p>
<p><code class="docutils literal notranslate"><span class="pre">=SUMIF($B$2:$G$2,I$2,B3:G3)</span></code></p>
<p><code class="docutils literal notranslate"><span class="pre">=SUMIF($B$2:$G$2,H$2,B3:G3)</span></code></p>
<p><code class="docutils literal notranslate"><span class="pre">$</span></code>在Excel中是绝对引用，公式中引用的单元格如果使用绝对引用
<code class="docutils literal notranslate"><span class="pre">$</span></code>，那么向下拖拽的过程中，这个单元格不会增加。</p>
<div class="figure align-default">
<img alt="" src="../_images/4.6.png" />
</div>
<p>例如，在J3单元格中输入<code class="docutils literal notranslate"><span class="pre">=C3</span></code>，在K3单元格中输入<code class="docutils literal notranslate"><span class="pre">=$C$3</span></code>并向下拖拽进行公式填充，观察区别</p>
<p>发现，如果使用绝对引用，引用格式值并不会随着公式的拖拽而改变，这就是绝对引用与非绝对引用的区别。</p>
<div class="figure align-default">
<img alt="" src="../_images/4.7.gif" />
</div>
</div>
<div class="section" id="header-n63">
<span id="id4"></span><h3>案例4<a class="headerlink" href="#header-n63" title="Permalink to this heading">#</a></h3>
<p>打开<code class="docutils literal notranslate"><span class="pre">data/chap4/4.2xlsx</span></code>中的【案例4】，现在需要统计语文成绩在80分以上，90分以下同学的数学成绩之和</p>
<p>那么遇到这种情况应该怎么处理呢？相比于前面的案例，这里有2个筛选条件，而SUMIF函数无法满足需求，这里可以使用SUMIFS函数</p>
<p>【SUMIFS函数】=SUMIFS（条件区域1，条件1，条件区域2，条件2）</p>
<p>因此写如下函数公式</p>
<p><strong>=SUMIFS(C2:C13,B2:B13,”&gt;80”,B2:B13,”&lt;90”)</strong></p>
<p>因为要求数学成绩，所以第一个参数为C2:C13，而筛选条件为语文，因此条件区域为B2:B13</p>
<div class="figure align-default">
<img alt="" src="../_images/4.8.gif" />
</div>
</div>
</div>
<div class="section" id="countifs">
<span id="header-n71"></span><h2>4.countifs函数<a class="headerlink" href="#countifs" title="Permalink to this heading">#</a></h2>
<p>打开<code class="docutils literal notranslate"><span class="pre">data/chap4/4.2xlsx</span></code>中的【案例5】，现在需要统计语文成绩超过60，数学成绩超过65，英语成绩超过70的同学个数</p>
<p>那么遇到这种情况应该怎么处理呢？其实countifs函数与sumifs函数用法非常接近，求满足所有条件的个数</p>
<p>【COUNTIFS函数】=COUNTIFS（条件区域1，条件1，条件区域2，条件2）</p>
<p>因此写如下函数公式</p>
<p><strong>=COUNTIFS(B2:B13,”&gt;60”,C2:C13,”&gt;65”,D2:D13,”&gt;70”)</strong></p>
<div class="figure align-default">
<img alt="" src="../_images/4.9.gif" />
</div>
</div>
<div class="section" id="header-n78">
<span id="id5"></span><h2>练习<a class="headerlink" href="#header-n78" title="Permalink to this heading">#</a></h2>
<p>1.选择<code class="docutils literal notranslate"><span class="pre">data/chap4/4.1.xlsx</span></code>工作簿中的【案例1】，将客户原始得分为1以下的校正为0</p>
<p>2.选择<code class="docutils literal notranslate"><span class="pre">data/chap4/4.1.xlsx</span></code>工作簿中的【案例2】，客户1月2月中只要有1个月消费超过400，即为”高消费客户”</p>
<p>3.选择<code class="docutils literal notranslate"><span class="pre">data/chap4/4.1.xlsx</span></code>工作簿中的【案例3】，能否不使用IFS函数，使用IF函数通过嵌套完成相应的务。</p>
<p>4.打开<code class="docutils literal notranslate"><span class="pre">data/chap4/4.2xlsx</span></code>中的【案例1】语文成绩在80分以上的同学成绩之和</p>
<p>5.打开<code class="docutils literal notranslate"><span class="pre">data/chap4/4.2xlsx</span></code>中的【案例2】统计所有苹果系列产品的销售额</p>
<p>6.打开<code class="docutils literal notranslate"><span class="pre">data/chap4/4.2xlsx</span></code>中的【案例6】完成所有手机的销售额求和</p>
</div>
</div>


              </article>
              

              
          </div>
          
      </div>
    </div>

  
  
  <!-- Scripts loaded after <body> so the DOM is not blocked -->
  <script src="../_static/scripts/pydata-sphinx-theme.js?digest=92025949c220c2e29695"></script>

<footer class="bd-footer"><div class="bd-footer__inner container">
  
  <div class="footer-item">
    <p class="copyright">
    &copy; Copyright 2022, Datawhale, 牧小熊.<br>
</p>
  </div>
  
  <div class="footer-item">
    <p class="sphinx-version">
Created using <a href="http://sphinx-doc.org/">Sphinx</a> 5.3.0.<br>
</p>
  </div>
  
</div>
</footer>
  </body>
</html>